﻿using Dapper;
using MySqlConnector;
using MySqlSync.Core.Interfaces;
using MySqlSync.Core.Models;
using System.ComponentModel;
using System.Data;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace MySqlSync.DataAccess
{
    /// <summary>
    /// 数据库服务实现
    /// </summary>
    public class DbService : IDbService
    {
        private readonly JsonSerializerOptions _jsonOptions = new JsonSerializerOptions
        {
            PropertyNameCaseInsensitive = true,
            WriteIndented = false, // 生产环境不缩进，节省空间
            Converters = {
                new JsonStringEnumConverter()  // 枚举转字符串的转换器
            }
        };

        /// <summary>
        /// 执行查询，返回数据阅读器
        /// </summary>
        public async Task<IDataReader> ExecuteReaderAsync(string connectionString, string sql, params object[] parameters)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException(nameof(connectionString));

            if (string.IsNullOrEmpty(sql))
                throw new ArgumentNullException(nameof(sql));

            var connection = new MySqlConnection(connectionString);
            try
            {
                await connection.OpenAsync();
                var command = new MySqlCommand(sql, connection);

                if (parameters != null && parameters.Length > 0)
                {
                    foreach (var param in parameters)
                    {
                        if (param is MySqlParameter mySqlParam)
                        {
                            command.Parameters.Add(mySqlParam);
                        }
                        else if (param is KeyValuePair<string, object> kvp)
                        {
                            command.Parameters.AddWithValue(kvp.Key, kvp.Value ?? DBNull.Value);
                        }
                        else
                        {
                            command.Parameters.AddWithValue($"param{command.Parameters.Count}", param ?? DBNull.Value);
                        }
                    }
                }

                // 使用CommandBehavior.CloseConnection确保 reader 关闭时连接也关闭
                return await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);
            }
            catch
            {
                // 如果打开连接失败，确保连接被释放
                connection.Dispose();
                throw;
            }
        }

        /// <summary>
        /// 执行非查询操作
        /// </summary>
        public async Task<int> ExecuteNonQueryAsync(string connectionString, string sql, object parameters = null)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException(nameof(connectionString));

            if (string.IsNullOrEmpty(sql))
                throw new ArgumentNullException(nameof(sql));

            using var connection = new MySqlConnection(connectionString);
            await connection.OpenAsync();
            // 直接传递参数对象
            return await connection.ExecuteAsync(sql, parameters);
        }

        /// <summary>
        /// 获取表的全量数据
        /// </summary>
        public async Task<IEnumerable<SyncRecord>> GetAllRecordsAsync(string connectionString, string tableName, int batchSize, long lastId = 0)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException(nameof(connectionString));

            if (string.IsNullOrEmpty(tableName))
                throw new ArgumentNullException(nameof(tableName));

            if (batchSize <= 0)
                throw new ArgumentOutOfRangeException(nameof(batchSize), "批量大小必须大于0");

            var primaryKey = await GetPrimaryKeyColumnNameAsync(connectionString, tableName);
            var sql = $"SELECT * FROM `{tableName}` WHERE `{primaryKey}` > @LastId ORDER BY `{primaryKey}` LIMIT @BatchSize";

            using var connection = new MySqlConnection(connectionString);
            var records = await connection.QueryAsync(sql, new { LastId = lastId, BatchSize = batchSize });

            return records.Select(record =>
            {
                var dict = (IDictionary<string, object>)record;
                var id = Convert.ToString(dict[primaryKey]) ?? string.Empty;

                // 尝试获取最后更新时间
                var updateTime = GetUpdateTime(dict);

                return new SyncRecord
                {
                    TableName = tableName,
                    Operation = "INSERT",
                    RecordId = id,
                    Data = JsonSerializer.Serialize(dict, _jsonOptions),
                    OperationTime = updateTime
                };
            });
        }

        /// <summary>
        /// 获取表的增量数据（基于UpdateTime字段）
        /// </summary>
        public async Task<IEnumerable<SyncRecord>> GetChangedRecordsAsync(string connectionString, string tableName, DateTime lastSyncTime)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException(nameof(connectionString));

            if (string.IsNullOrEmpty(tableName))
                throw new ArgumentNullException(nameof(tableName));

            var primaryKey = await GetPrimaryKeyColumnNameAsync(connectionString, tableName);

            // 表中有UpdateTime字段用于跟踪变更
            var sql = $@"SELECT * FROM `{tableName}` 
                     WHERE UpdateTime > @LastSyncTime 
                     ORDER BY UpdateTime ASC";

            using var connection = new MySqlConnection(connectionString);
            var records = await connection.QueryAsync(sql, new { LastSyncTime = lastSyncTime });

            return records.Select(record =>
            {
                var dict = (IDictionary<string, object>)record;
                var id = Convert.ToString(dict[primaryKey]) ?? string.Empty;
                var updateTime = GetUpdateTime(dict);

                return new SyncRecord
                {
                    TableName = tableName,
                    Operation = "UPDATE",
                    RecordId = id,
                    Data = JsonSerializer.Serialize(dict, _jsonOptions),
                    OperationTime = updateTime
                };
            });
        }

        /// <summary>
        /// 应用同步记录到目标数据库
        /// </summary>
        public async Task ApplySyncRecordAsync(SyncRecord record, string targetConnectionString)
        {
            if (record == null)
                throw new ArgumentNullException(nameof(record));

            if (string.IsNullOrEmpty(targetConnectionString))
                throw new ArgumentNullException(nameof(targetConnectionString));

            if (string.IsNullOrEmpty(record.Data))
                throw new ArgumentException("同步记录数据不能为空", nameof(record));

            // 检查目标表是否存在
            if (!await TableExistsAsync(targetConnectionString, record.TableName))
            {
                throw new InvalidOperationException($"目标数据库中不存在表: {record.TableName}");
            }

            var primaryKey = await GetPrimaryKeyColumnNameAsync(targetConnectionString, record.TableName);
            var data = JsonSerializer.Deserialize<Dictionary<string, object>>(record.Data, _jsonOptions);

            // 获取目标表的所有字段
            var tableColumns = await GetTableColumnsAsync(targetConnectionString, record.TableName);
            // 检查数据中是否有表不存在的字段
            var invalidColumns = data.Keys.Where(k => !tableColumns.Contains(k)).ToList();
            if (invalidColumns.Any())
            {
                throw new InvalidOperationException($"数据包含目标表不存在的字段：{string.Join(",", invalidColumns)}");
            }

            if (data == null || !data.ContainsKey(primaryKey))
                throw new InvalidOperationException($"无法解析同步记录数据或找不到主键 {primaryKey}");

            switch (record.Operation.ToUpper())
            {
                case "INSERT":
                    await InsertRecordAsync(targetConnectionString, record.TableName, data);
                    break;
                case "UPDATE":
                    await UpdateRecordAsync(targetConnectionString, record.TableName, data, primaryKey);
                    break;
                case "DELETE":
                    await DeleteRecordAsync(targetConnectionString, record.TableName, data[primaryKey], primaryKey);
                    break;
                default:
                    throw new NotSupportedException($"不支持的操作类型: {record.Operation}");
            }
        }

        private async Task<List<string>> GetTableColumnsAsync(string connectionString, string tableName)
        {
            using var connection = new MySqlConnection(connectionString);
            var sql = @"
            SELECT COLUMN_NAME 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = @TableName";
            return (await connection.QueryAsync<string>(sql, new { TableName = tableName })).ToList();
        }

        /// <summary>
        /// 获取表的主键列名
        /// </summary>
        public async Task<string> GetPrimaryKeyColumnNameAsync(string connectionString, string tableName)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException(nameof(connectionString));

            if (string.IsNullOrEmpty(tableName))
                throw new ArgumentNullException(nameof(tableName));

            using var connection = new MySqlConnection(connectionString);
            var sql = @"
            SELECT COLUMN_NAME 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
              AND TABLE_NAME = @TableName 
              AND COLUMN_KEY = 'PRI'";

            var primaryKey = await connection.QueryFirstOrDefaultAsync<string>(sql, new { TableName = tableName });

            if (string.IsNullOrEmpty(primaryKey))
                throw new InvalidOperationException($"表 {tableName} 没有找到主键");

            return primaryKey;
        }

        /// <summary>
        /// 检查表是否存在
        /// </summary>
        public async Task<bool> TableExistsAsync(string connectionString, string tableName)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException(nameof(connectionString));

            if (string.IsNullOrEmpty(tableName))
                throw new ArgumentNullException(nameof(tableName));

            using var connection = new MySqlConnection(connectionString);
            var sql = @"
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = DATABASE() 
              AND TABLE_NAME = @TableName";

            var count = await connection.QueryFirstAsync<int>(sql, new { TableName = tableName });
            return count > 0;
        }

        private async Task InsertRecordAsync(string connectionString, string tableName, Dictionary<string, object> data)
        {
            try
            {
                var columns = string.Join(", ", data.Keys.Select(k => $"`{k}`"));
                var parameters = string.Join(", ", data.Keys.Select(k => $"@{k}"));
                var sql = $"INSERT IGNORE INTO `{tableName}` ({columns}) VALUES ({parameters});";

                var dynamicParams = new DynamicParameters();
                foreach (var (key, value) in data)
                {
                    if (value is JsonElement jsonElement)
                    {
                        dynamicParams.Add($"@{key}", jsonElement.ToString());
                    }
                    else if (value == DBNull.Value)
                    {
                        dynamicParams.Add($"@{key}", null);
                    }
                    else
                    {
                        dynamicParams.Add($"@{key}", value);
                    }
                }

                Console.WriteLine($"执行INSERT SQL: {sql}");
                Console.WriteLine($"参数数量: {data.Count}");
                await ExecuteNonQueryAsync(connectionString, sql, dynamicParams);
            }
            catch (MySqlException mySqlEx)
            {
                Console.WriteLine($"MySQL错误: 代码={mySqlEx.Number}, 消息={mySqlEx.Message}");
                Console.WriteLine($"堆栈跟踪: {mySqlEx.StackTrace}");
                throw;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入失败 类: {ex.GetType().Name}, 消息: {ex.Message}");
                Console.WriteLine($"堆栈跟踪: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        private async Task UpdateRecordAsync(string connectionString, string tableName, Dictionary<string, object> data, string primaryKey)
        {
            var setClause = string.Join(", ", data.Keys.Where(k => !k.Equals(primaryKey, StringComparison.OrdinalIgnoreCase))
                .Select(k => $"`{k}` = @{k}"));

            var sql = $"UPDATE `{tableName}` SET {setClause} WHERE `{primaryKey}` = @{primaryKey}";

            var affectedRows = await ExecuteNonQueryAsync(connectionString, sql, data);

            // 如果没有更新到记录，尝试插入
            if (affectedRows == 0)
            {
                await InsertRecordAsync(connectionString, tableName, data);
            }
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        private async Task DeleteRecordAsync(string connectionString, string tableName, object id, string primaryKey)
        {
            var sql = $"DELETE FROM `{tableName}` WHERE `{primaryKey}` = @Id";
            await ExecuteNonQueryAsync(connectionString, sql, new { Id = id });
        }

        /// <summary>
        /// 获取更新时间
        /// </summary>
        private DateTime GetUpdateTime(IDictionary<string, object> record)
        {
            // 尝试从常见的时间戳字段获取更新时间
            if (record.TryGetValue("UpdateTime", out var updateTimeObj) && updateTimeObj != null)
            {
                if (updateTimeObj is DateTime dt)
                    return dt;

                if (DateTime.TryParse(updateTimeObj.ToString(), out dt))
                    return dt;
            }

            // 尝试创建时间
            if (record.TryGetValue("CreateTime", out var createTimeObj) && createTimeObj != null)
            {
                if (createTimeObj is DateTime dt)
                    return dt;

                if (DateTime.TryParse(createTimeObj.ToString(), out dt))
                    return dt;
            }

            // 如果都没有，使用当前时间
            return DateTime.Now;
        }
    }
}
